package com.bw.dao;

import com.bw.domain.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Controller;

import java.util.List;

@Mapper
public interface SugarDao {

    @Select("SELECT b.`name`,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN base_province b ON  o.province_id = b.id\n" +
            "GROUP BY b.name ORDER BY value desc")
    List<Sugar> get1();

    @Select("CALL p1")
    double get2();

    @Select("SELECT t.name,  sum(o.final_total_amount)  VALUE FROM order_info o\n" +
            "INNER JOIN order_detail d ON  o.id = d.order_id\n" +
            "INNER JOIN sku_info s ON  d.sku_id = s.id\n" +
            "INNER JOIN base_trademark t ON  s.tm_id = t.id\n" +
            "GROUP BY t.name ORDER BY VALUE DESC")
    List<Sugar> get3();

    @Select("SELECT  c1.`name`,sum(o.final_total_amount)VALUE FROM order_info o\n" +
            "INNER JOIN order_detail d ON  o.id = d.order_id\n" +
            "INNER JOIN sku_info s ON  d.sku_id = s.id\n" +
            "INNER JOIN base_category3 c3 ON  s.category3_id = c3.id\n" +
            "INNER JOIN base_category2  c2 ON  c3.category2_id = c2.id\n" +
            "INNER JOIN base_category1  c1 ON  c2.category1_id = c1.id\n" +
            "GROUP BY c1.`name` ORDER BY VALUE desc")
    List<Sugar> get4();

    @Select("select p.spu_name name,sum(o.final_total_amount) value  from  order_info o\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN  spu_info p on  s.spu_id = p.id\n" +
            "GROUP BY p.spu_name")
    List<Sugar> get5();
}
